package org.hfy.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hfy.entity.Contract;
import org.hfy.entity.Quotation;
import org.hfy.entity.QuotationItem;
import org.springframework.core.io.ClassPathResource;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * Excel合同生成器 - 基于模板生成
 * 
 * @author hfy
 * @version 1.0
 */
@Slf4j
public class ExcelContractGenerator {

    private static final String TEMPLATE_PATH = "templates/合同模板.xlsx";
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    
    /**
     * 根据合同模板生成Excel合同
     * 
     * @param contract 合同信息
     * @return Excel文件字节数组
     */
    public static byte[] generateContractExcel(Contract contract) {
        try (InputStream templateInputStream = new ClassPathResource(TEMPLATE_PATH).getInputStream();
             XSSFWorkbook workbook = new XSSFWorkbook(templateInputStream);
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Quotation quotation = contract.getQuotation();
            if (quotation == null) {
                throw new IllegalArgumentException("合同关联的报价单信息不能为空");
            }
            
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            
            // 根据模板内容填充数据
            fillContractData(sheet, contract, quotation);
            
            workbook.write(outputStream);
            return outputStream.toByteArray();
            
        } catch (Exception e) {
            log.error("基于模板生成Excel合同失败: {}", e.getMessage(), e);
            // 如果模板加载失败，使用备用方法生成
            return generateContractExcelFallback(contract);
        }
    }
    
    /**
     * 填充合同数据到模板
     */
    private static void fillContractData(Sheet sheet, Contract contract, Quotation quotation) {
        try {
            // 遍历所有行和单元格，查找占位符并替换
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;
                
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    if (cell == null || cell.getCellType() != CellType.STRING) continue;
                    
                    String cellValue = cell.getStringCellValue();
                    if (cellValue == null || cellValue.trim().isEmpty()) continue;
                    
                    // 替换占位符
                    String newValue = replacePlaceholders(cellValue, contract, quotation);
                    if (!cellValue.equals(newValue)) {
                        cell.setCellValue(newValue);
                    }
                }
            }
            
            // 如果模板中有产品明细表，填充产品数据
            fillProductDetailsIfExists(sheet, quotation.getItems());
            
        } catch (Exception e) {
            log.error("填充合同数据失败: {}", e.getMessage(), e);
        }
    }
    
    /**
     * 替换占位符
     */
    private static String replacePlaceholders(String text, Contract contract, Quotation quotation) {
        if (text == null) return "";
        
        // 合同相关占位符
        text = text.replace("${合同编号}", contract.getContractNo() != null ? contract.getContractNo() : "");
        text = text.replace("${contractNo}", contract.getContractNo() != null ? contract.getContractNo() : "");
        
        // 客户相关占位符
        text = text.replace("${客户名称}", quotation.getCustomerName() != null ? quotation.getCustomerName() : "");
        text = text.replace("${customerName}", quotation.getCustomerName() != null ? quotation.getCustomerName() : "");
        text = text.replace("${客户代码}", quotation.getCustomerCode() != null ? quotation.getCustomerCode() : "");
        text = text.replace("${customerCode}", quotation.getCustomerCode() != null ? quotation.getCustomerCode() : "");
        
        // 业务员相关占位符
        text = text.replace("${业务员}", quotation.getSalesperson() != null ? quotation.getSalesperson() : "");
        text = text.replace("${salesperson}", quotation.getSalesperson() != null ? quotation.getSalesperson() : "");
        
        // 日期相关占位符
        text = text.replace("${订单日期}", quotation.getOrderDate() != null ? quotation.getOrderDate().format(DATE_FORMATTER) : "");
        text = text.replace("${orderDate}", quotation.getOrderDate() != null ? quotation.getOrderDate().format(DATE_FORMATTER) : "");
        text = text.replace("${当前日期}", LocalDate.now().format(DATE_FORMATTER));
        text = text.replace("${currentDate}", LocalDate.now().format(DATE_FORMATTER));
        
        // 金额相关占位符
        BigDecimal totalAmount = quotation.getTotalAmount() != null ? quotation.getTotalAmount() : BigDecimal.ZERO;
        text = text.replace("${总金额}", formatAmount(totalAmount));
        text = text.replace("${totalAmount}", formatAmount(totalAmount));
        text = text.replace("${大写金额}", convertNumberToWords(totalAmount));
        text = text.replace("${totalAmountWords}", convertNumberToWords(totalAmount));
        
        // 其他占位符
        text = text.replace("${付款方式}", quotation.getPaymentMethod() != null ? quotation.getPaymentMethod() : "");
        text = text.replace("${paymentMethod}", quotation.getPaymentMethod() != null ? quotation.getPaymentMethod() : "");
        text = text.replace("${是否含税}", Boolean.TRUE.equals(quotation.getIsTaxIncluded()) ? "是（增值税票13%）" : "否");
        text = text.replace("${isTaxIncluded}", Boolean.TRUE.equals(quotation.getIsTaxIncluded()) ? "是（增值税票13%）" : "否");
        
        // 物料相关统计占位符
        if (quotation.getItems() != null && !quotation.getItems().isEmpty()) {
            text = text.replace("${物料总数}", String.valueOf(quotation.getItems().size()));
            text = text.replace("${itemCount}", String.valueOf(quotation.getItems().size()));
            
            int totalQuantity = quotation.getItems().stream().mapToInt(item -> item.getQuantity() != null ? item.getQuantity() : 0).sum();
            text = text.replace("${总数量}", String.valueOf(totalQuantity));
            text = text.replace("${totalQuantity}", String.valueOf(totalQuantity));
            
            BigDecimal totalTestingFee = quotation.getItems().stream()
                .map(item -> item.getTestingFee() != null ? item.getTestingFee() : BigDecimal.ZERO)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
            text = text.replace("${总测试架费}", formatAmount(totalTestingFee));
            text = text.replace("${totalTestingFee}", formatAmount(totalTestingFee));
            
            BigDecimal totalEngineeringFee = quotation.getItems().stream()
                .map(item -> item.getCosts() != null && item.getCosts().getEngineeringFee() != null ? 
                     item.getCosts().getEngineeringFee() : BigDecimal.ZERO)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
            text = text.replace("${总工程费}", formatAmount(totalEngineeringFee));
            text = text.replace("${totalEngineeringFee}", formatAmount(totalEngineeringFee));
            
            BigDecimal totalSampleFee = quotation.getItems().stream()
                .map(item -> item.getSampleFee() != null ? item.getSampleFee() : BigDecimal.ZERO)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
            text = text.replace("${总样板费}", formatAmount(totalSampleFee));
            text = text.replace("${totalSampleFee}", formatAmount(totalSampleFee));
        } else {
            text = text.replace("${物料总数}", "0");
            text = text.replace("${itemCount}", "0");
            text = text.replace("${总数量}", "0");
            text = text.replace("${totalQuantity}", "0");
            text = text.replace("${总测试架费}", "0.00");
            text = text.replace("${totalTestingFee}", "0.00");
            text = text.replace("${总工程费}", "0.00");
            text = text.replace("${totalEngineeringFee}", "0.00");
            text = text.replace("${总样板费}", "0.00");
            text = text.replace("${totalSampleFee}", "0.00");
        }
        
        return text;
    }
    
    /**
     * 填充产品明细数据（如果模板中存在产品明细表）
     */
    private static void fillProductDetailsIfExists(Sheet sheet, List<QuotationItem> items) {
        if (items == null || items.isEmpty()) return;
        
        try {
            // 查找产品明细表的开始位置
            int[] tableInfo = findProductTableInfo(sheet);
            int productTableStartRow = tableInfo[0];
            int[] columnMapping = getColumnMapping(sheet, productTableStartRow);
            
            if (productTableStartRow == -1) {
                log.info("模板中未找到产品明细表，跳过产品数据填充");
                return;
            }
            
            // 从产品表开始的下一行填充数据
            int dataStartRow = productTableStartRow + 1;
            for (int i = 0; i < items.size(); i++) {
                QuotationItem item = items.get(i);
                int currentRow = dataStartRow + i;
                
                // 确保行存在
                Row row = sheet.getRow(currentRow);
                if (row == null) {
                    row = sheet.createRow(currentRow);
                }
                
                // 根据列映射填充完整的产品数据
                fillItemDataToRow(row, item, i + 1, columnMapping);
            }
            
        } catch (Exception e) {
            log.error("填充产品明细数据失败: {}", e.getMessage(), e);
        }
    }
    
    /**
     * 获取产品表信息（表头行号和列映射）
     */
    private static int[] findProductTableInfo(Sheet sheet) {
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;
            
            // 检查是否包含产品表头关键字
            boolean foundProductTable = false;
            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                if (cell == null || cell.getCellType() != CellType.STRING) continue;
                
                String cellValue = cell.getStringCellValue();
                if (cellValue != null && (cellValue.contains("序号") || cellValue.contains("产品名称") || 
                    cellValue.contains("物料编码") || cellValue.contains("数量"))) {
                    foundProductTable = true;
                    break;
                }
            }
            
            if (foundProductTable) {
                return new int[]{rowIndex, 0}; // 返回行号
            }
        }
        return new int[]{-1, 0};
    }
    
    /**
     * 获取列映射关系（字段名 -> 列索引）
     */
    private static int[] getColumnMapping(Sheet sheet, int headerRow) {
        // 初始化列映射数组：[序号列, 产品名称列, 物料编码列, 板厚列, 外铜列, 内铜列, 交货尺寸列, 拼板列, 
        //                  表面处理列, 阻焊列, 字符列, 过孔工艺列, 测试列, 数量列, 单价列, 测试架费列, 
        //                  工程费列, 样板费列, 合计金额列, 交期列, 备注列]
        int[] columnMapping = new int[21];
        for (int i = 0; i < columnMapping.length; i++) {
            columnMapping[i] = -1; // 初始化为-1表示未找到
        }
        
        if (headerRow == -1) return columnMapping;
        
        Row row = sheet.getRow(headerRow);
        if (row == null) return columnMapping;
        
        // 遍历表头行，建立列映射
        for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
            Cell cell = row.getCell(cellIndex);
            if (cell == null || cell.getCellType() != CellType.STRING) continue;
            
            String cellValue = cell.getStringCellValue();
            if (cellValue == null) continue;
            
            // 建立列映射
            if (cellValue.contains("序号")) columnMapping[0] = cellIndex;
            else if (cellValue.contains("产品名称")) columnMapping[1] = cellIndex;
            else if (cellValue.contains("物料编码")) columnMapping[2] = cellIndex;
            else if (cellValue.contains("板厚")) columnMapping[3] = cellIndex;
            else if (cellValue.contains("外铜")) columnMapping[4] = cellIndex;
            else if (cellValue.contains("内铜")) columnMapping[5] = cellIndex;
            else if (cellValue.contains("交货尺寸")) columnMapping[6] = cellIndex;
            else if (cellValue.contains("拼板")) columnMapping[7] = cellIndex;
            else if (cellValue.contains("表面处理")) columnMapping[8] = cellIndex;
            else if (cellValue.contains("阻焊")) columnMapping[9] = cellIndex;
            else if (cellValue.contains("字符")) columnMapping[10] = cellIndex;
            else if (cellValue.contains("过孔工艺")) columnMapping[11] = cellIndex;
            else if (cellValue.contains("测试") && !cellValue.contains("费")) columnMapping[12] = cellIndex;
            else if (cellValue.contains("数量")) columnMapping[13] = cellIndex;
            else if (cellValue.contains("单价")) columnMapping[14] = cellIndex;
            else if (cellValue.contains("测试架费")) columnMapping[15] = cellIndex;
            else if (cellValue.contains("工程费")) columnMapping[16] = cellIndex;
            else if (cellValue.contains("样板费")) columnMapping[17] = cellIndex;
            else if (cellValue.contains("合计金额") || cellValue.contains("金额")) columnMapping[18] = cellIndex;
            else if (cellValue.contains("交期")) columnMapping[19] = cellIndex;
            else if (cellValue.contains("备注")) columnMapping[20] = cellIndex;
        }
        
        return columnMapping;
    }
    
    /**
     * 将物料数据填充到指定行
     */
    private static void fillItemDataToRow(Row row, QuotationItem item, int itemNo, int[] columnMapping) {
        // 序号
        if (columnMapping[0] != -1) {
            setCellValue(row, columnMapping[0], String.valueOf(itemNo));
        }
        
        // 产品名称
        if (columnMapping[1] != -1) {
            setCellValue(row, columnMapping[1], item.getProductName());
        }
        
        // 物料编码
        if (columnMapping[2] != -1) {
            setCellValue(row, columnMapping[2], item.getMaterialCode());
        }
        
        // 板厚(mm)
        if (columnMapping[3] != -1) {
            setCellValue(row, columnMapping[3], item.getThickness());
        }
        
        // 外铜
        if (columnMapping[4] != -1) {
            setCellValue(row, columnMapping[4], item.getOuterCopper());
        }
        
        // 内铜
        if (columnMapping[5] != -1) {
            setCellValue(row, columnMapping[5], item.getInnerCopper());
        }
        
        // 交货尺寸(mm)
        if (columnMapping[6] != -1) {
            setCellValue(row, columnMapping[6], item.getDeliverySize());
        }
        
        // 拼板
        if (columnMapping[7] != -1) {
            setCellValue(row, columnMapping[7], item.getPanelization());
        }
        
        // 表面处理
        if (columnMapping[8] != -1) {
            setCellValue(row, columnMapping[8], item.getSurfaceTreatment());
        }
        
        // 阻焊
        if (columnMapping[9] != -1) {
            setCellValue(row, columnMapping[9], item.getSolderMask());
        }
        
        // 字符
        if (columnMapping[10] != -1) {
            setCellValue(row, columnMapping[10], item.getCharacter());
        }
        
        // 过孔工艺
        if (columnMapping[11] != -1) {
            setCellValue(row, columnMapping[11], item.getViaProcess());
        }
        
        // 测试
        if (columnMapping[12] != -1) {
            setCellValue(row, columnMapping[12], item.getTesting());
        }
        
        // 数量
        if (columnMapping[13] != -1) {
            setCellValue(row, columnMapping[13], String.valueOf(item.getQuantity()));
        }
        
        // 单价
        if (columnMapping[14] != -1) {
            setCellValue(row, columnMapping[14], formatAmount(item.getUnitPrice()));
        }
        
        // 测试架费
        if (columnMapping[15] != -1) {
            setCellValue(row, columnMapping[15], formatAmount(item.getTestingFee()));
        }
        
        // 工程费
        if (columnMapping[16] != -1) {
            BigDecimal engineeringFee = item.getCosts() != null ? item.getCosts().getEngineeringFee() : null;
            setCellValue(row, columnMapping[16], formatAmount(engineeringFee));
        }
        
        // 样板费
        if (columnMapping[17] != -1) {
            setCellValue(row, columnMapping[17], formatAmount(item.getSampleFee()));
        }
        
        // 合计金额
        if (columnMapping[18] != -1) {
            setCellValue(row, columnMapping[18], formatAmount(item.getTotalAmount()));
        }
        
        // 交期
        if (columnMapping[19] != -1) {
            setCellValue(row, columnMapping[19], item.getDeliveryDate() != null ? 
                item.getDeliveryDate().format(DATE_FORMATTER) : "");
        }
        
        // 备注
        if (columnMapping[20] != -1) {
            setCellValue(row, columnMapping[20], item.getItemRemark());
        }
    }
    

    
    /**
     * 设置单元格值
     */
    private static void setCellValue(Row row, int columnIndex, String value) {
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            cell = row.createCell(columnIndex);
        }
        cell.setCellValue(value != null ? value : "");
    }
    
    /**
     * 备用方法：如果模板加载失败，使用原有逻辑生成
     */
    private static byte[] generateContractExcelFallback(Contract contract) {
        try (XSSFWorkbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Quotation quotation = contract.getQuotation();
            Sheet sheet = workbook.createSheet("销售合同");
            
            // 使用简化的合同格式
            createSimpleContract(sheet, contract, quotation);
            
            workbook.write(outputStream);
            return outputStream.toByteArray();
            
        } catch (Exception e) {
            log.error("备用方法生成Excel合同失败: {}", e.getMessage(), e);
            throw new RuntimeException("生成Excel合同失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 创建简化合同格式
     */
    private static void createSimpleContract(Sheet sheet, Contract contract, Quotation quotation) {
        int rowIndex = 0;
        
        // 标题
        setCellValue(sheet, rowIndex++, 0, "PCB印制电路板加工合同");
        
        // 基本信息
        setCellValue(sheet, rowIndex++, 0, "合同编号：" + (contract.getContractNo() != null ? contract.getContractNo() : ""));
        setCellValue(sheet, rowIndex++, 0, "客户名称：" + (quotation.getCustomerName() != null ? quotation.getCustomerName() : ""));
        setCellValue(sheet, rowIndex++, 0, "业务员：" + (quotation.getSalesperson() != null ? quotation.getSalesperson() : ""));
        setCellValue(sheet, rowIndex++, 0, "订单日期：" + (quotation.getOrderDate() != null ? quotation.getOrderDate().format(DATE_FORMATTER) : ""));
        
        rowIndex++; // 空行
        
        // 产品明细表头
        setCellValue(sheet, rowIndex, 0, "序号");
        setCellValue(sheet, rowIndex, 1, "产品名称");
        setCellValue(sheet, rowIndex, 2, "数量");
        setCellValue(sheet, rowIndex, 3, "单价");
        setCellValue(sheet, rowIndex++, 4, "金额");
        
        // 产品数据
        if (quotation.getItems() != null) {
            for (int i = 0; i < quotation.getItems().size(); i++) {
                QuotationItem item = quotation.getItems().get(i);
                setCellValue(sheet, rowIndex, 0, String.valueOf(i + 1));
                setCellValue(sheet, rowIndex, 1, item.getProductName());
                setCellValue(sheet, rowIndex, 2, String.valueOf(item.getQuantity()));
                setCellValue(sheet, rowIndex, 3, formatAmount(item.getUnitPrice()));
                setCellValue(sheet, rowIndex++, 4, formatAmount(item.getTotalAmount()));
            }
        }
        
        rowIndex++; // 空行
        
        // 总金额
        BigDecimal totalAmount = quotation.getTotalAmount() != null ? quotation.getTotalAmount() : BigDecimal.ZERO;
        setCellValue(sheet, rowIndex++, 0, "合同总金额：" + formatAmount(totalAmount) + " 元");
        setCellValue(sheet, rowIndex++, 0, "大写金额：" + convertNumberToWords(totalAmount));
        
        // 设置列宽
        for (int i = 0; i < 5; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    
    /**
     * 设置工作表单元格值
     */
    private static void setCellValue(Sheet sheet, int rowIndex, int columnIndex, String value) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            cell = row.createCell(columnIndex);
        }
        cell.setCellValue(value != null ? value : "");
    }
    
    /**
     * 格式化金额
     */
    private static String formatAmount(BigDecimal amount) {
        if (amount == null) return "0.00";
        return String.format("%.2f", amount);
    }
    
    /**
     * 数字转中文大写
     */
    private static String convertNumberToWords(BigDecimal amount) {
        if (amount == null) return "零元整";
        
        String[] units = {"", "十", "百", "千", "万", "十万", "百万", "千万", "亿"};
        String[] nums = {"零", "一", "二", "三", "四", "五", "六", "七", "八", "九"};
        
        long intPart = amount.longValue();
        int decimalPart = amount.remainder(BigDecimal.ONE).multiply(new BigDecimal(100)).intValue();
        
        if (intPart == 0 && decimalPart == 0) {
            return "零元整";
        }
        
        StringBuilder result = new StringBuilder();
        
        // 处理整数部分
        if (intPart > 0) {
            String intStr = String.valueOf(intPart);
            for (int i = 0; i < intStr.length(); i++) {
                int digit = Integer.parseInt(String.valueOf(intStr.charAt(i)));
                if (digit != 0) {
                    result.append(nums[digit]);
                    int unitIndex = intStr.length() - i - 1;
                    if (unitIndex < units.length) {
                        result.append(units[unitIndex]);
                    }
                }
            }
            result.append("元");
        }
        
        // 处理小数部分
        if (decimalPart > 0) {
            int jiao = decimalPart / 10;
            int fen = decimalPart % 10;
            if (jiao > 0) {
                result.append(nums[jiao]).append("角");
            }
            if (fen > 0) {
                result.append(nums[fen]).append("分");
            }
        } else {
            result.append("整");
        }
        
        return result.toString();
    }
} 